﻿using CrystalDecisions.Shared.Json;
using DataAccess.DBConnections;
using DataAccess.ProxyConnections;
using Oracle.DataAccess.Client;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess.SingleSignOn
{
    public class AuthenConnection
    {
        DBConnectionsEngine dbEngine;
        OracleConnection Conn;
        Hashtable hashtable;
        string strSql;

        public AuthenConnection()
        {

        }

        #region " | Main | "

        public DataTable getAppTokenData(string token)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@" 
            SELECT APPTOKEN,APPLNO,APPLNAME,EMPNO,PASSWORD FROM APPTOKEN WHERE TOKEN= :a ");
            hashtable = new Hashtable();
            hashtable.Add(":a", token);

            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }
        public int DeleteToken(int pAppTokenId)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = " DELETE FROM APPTOKEN WHERE APPTOKEN=" + (object)pAppTokenId;
            return dbEngine.ExecuteNonQuery(strSql, Conn);

        }

        public DataTable getProfileData(string username)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@" 
            SELECT APPTOKEN,APPLNO,APPLNAME,EMPNO,PASSWORD FROM APPTOKEN WHERE EMPNO= :username");
            hashtable = new Hashtable();
            hashtable.Add(":username", username);

            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }

        public DataTable getProfileData(string username, string password)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@" 
            SELECT APPTOKEN,APPLNO,APPLNAME,EMPNO,PASSWORD FROM APPTOKEN WHERE EMPNO= :username AND PASSWORD= :password ");
            hashtable = new Hashtable();
            hashtable.Add(":username", username);
            hashtable.Add(":password", password);
            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }

        public DataTable getUserData(string username)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"
            SELECT d.DESCRIPTION,c.GROUPDESC,a.EMPNO,a.EMPNAME,a.PASSWORD,
            b.GROUPNO,a.DIVISION,b.STORENO,a.TEL,a.EMAIL,
            a.STATUS,b.APPLNO,a.EMPID ,b.PROFILELEVEL,a.LANG
            FROM USER_PROFILE a,
            USER_PROFILE_GROUP b,
            FUNCTION_GROUP c,
            APPLICATION d 
            WHERE a.EMPNO=b.EMPNO AND b.APPLNO=c.APPLNO 
            AND b.GROUPNO=c.GROUPNO AND c.APPLNO=d.APPLNO 
            AND UPPER(a.STATUS)='A'
            AND LOWER(a.EMPNO)=  :username  ");

            hashtable = new Hashtable();
            hashtable.Add(":username", username);


            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }
        
        public String getGroup(string username, string applNo, string company)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"
            SELECT GROUPNO
            FROM USER_PROFILE_GROUP
            WHERE LOWER(EMPNO)=  :username
            AND APPLNO=  :applno
            AND Company_code=  :company ");

            hashtable = new Hashtable();
            hashtable.Add(":username", username);
            hashtable.Add(":applno", applNo);
            hashtable.Add(":company", company);

            return (String)dbEngine.GetDataTable(strSql, hashtable, Conn).Rows[0]["GROUPNO"];
        }

        public DataTable getCompany(string username,string concast = null)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"SELECT company_code,company_name_en,company_name_abbr
                    FROM company 
                    WHERE 1=1
                    AND company_code IN (SELECT comp_code FROM user_profile WHERE empno=:username AND status='A')
                    AND active_flag='Y' {0}
                    ORDER BY company_code ASC", concast);

            hashtable = new Hashtable();
            hashtable.Add(":username", username);

            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }

        public DataTable getCompanyName(string company_code)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"SELECT company_name_en FROM company WHERE company_code=:company_code");
            hashtable = new Hashtable();
            hashtable.Add(":company_code", company_code);

            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }

        public DataTable getLanguage()
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"SELECT lang_culture_name, lang_name, lang_name_abbr  FROM application_language ");


            return dbEngine.GetDataTable(strSql, Conn);
        }

        public Object getCompanyLogo(string company = "HP00")
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"SELECT logo FROM company WHERE company_code='{0}' ", company);
            return (Object)(dbEngine.ExecuteScalar(strSql, Conn));
        }

        public DataTable pullUserProfile(string username)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"
            SELECT d.DESCRIPTION,c.GROUPDESC,a.EMPNO,a.EMPNAME,a.PASSWORD,
            b.GROUPNO,a.DIVISION,b.STORENO,a.TEL,a.EMAIL,
            a.STATUS,b.APPLNO,a.EMPID ,b.PROFILELEVEL,a.LANG
            FROM USER_PROFILE a,
            USER_PROFILE_GROUP b,
            FUNCTION_GROUP c,
            APPLICATION d 
            WHERE a.EMPNO=b.EMPNO AND b.APPLNO=c.APPLNO 
            AND b.GROUPNO=c.GROUPNO AND c.APPLNO=d.APPLNO 
            AND LOWER(a.EMPID)=:username  ");

            hashtable = new Hashtable();
            hashtable.Add(":username", username);


            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }

        public DataTable pullUserProfile(string username, string password)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"
            SELECT a.EMPNO,a.EMPNAME,a.PASSWORD,
            a.DIVISION,a.TEL,a.EMAIL,
            a.STATUS,a.EMPID ,a.LANG
            FROM USER_PROFILE a
            WHERE a.EMPID=:username 
            AND a.PASSWORD=:password");

            hashtable = new Hashtable();
            hashtable.Add(":username", username);
            hashtable.Add(":password", password);

            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }

        public Boolean upChangePassword(string username, string password)
        {
            try
            {
                dbEngine = new DBConnectionsEngine();
                Conn = new DatabaseProxy().getAUTHConnection();
                strSql = String.Format(@"
                            UPDATE USER_PROFILE
                            SET PASSWORD='{1}'
                            , UPDDATE =sysdate
                            , UPDBY='{0}'
                            WHERE LOWER(EMPID)=  '{0}'
                            AND PASSWORD='{1}'
                            ", username, password);
                dbEngine.ExecuteNonQuery(strSql, Conn);

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public Boolean upUserCurrentUI(string username, string language, string company)
        {
            try
            {
                dbEngine = new DBConnectionsEngine();
                Conn = new DatabaseProxy().getAUTHConnection();
                strSql = String.Format(@"
                            UPDATE USER_PROFILE
                            SET LANG='{1}'
                            , UPDDATE =sysdate
                            , UPDBY='{0}'
                            WHERE LOWER(EMPNO)=  '{0}'
                            AND COMP_CODE='{2}'
                            ", username, language,company);
                dbEngine.ExecuteNonQuery(strSql, Conn);

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        #endregion

        #region " | WWW Application | "

        public DataTable getApplicationList(string username, string company)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"
                SELECT a.applno,a.applname,a.description,a.url
                FROM user_profile up
                INNER JOIN user_profile_group upg ON up.empno=upg.empno  
                AND up.comp_code = upg.company_code
                INNER JOIN application a ON upg.applno=a.applno
                AND upg.company_code = a.company_code
                WHERE 1=1 
                AND up.empno =:username
                AND up.comp_code =:company
                AND a.status='Y'
                AND upg.status='Y'
                AND up.status='A'
                ORDER BY a.applname ASC
            ");

            hashtable = new Hashtable();
            hashtable.Add(":username", username);
            hashtable.Add(":company", company);
            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }

        public DataTable getApplicationListByApplNo(string username, string company, string applNo)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"
                SELECT a.applno,a.applname,a.description,a.url
                FROM user_profile up
                INNER JOIN user_profile_group upg ON up.empno=upg.empno  
                AND up.comp_code = upg.company_code
                INNER JOIN application a ON upg.applno=a.applno
                AND upg.company_code = a.company_code
                WHERE 1=1 
                AND up.empno =:username
                AND up.comp_code =:company
                AND upg.applno =:applno
                AND a.status='Y'
                AND upg.status='Y'
                AND up.status='A'
                ORDER BY a.applname ASC
            ");

            hashtable = new Hashtable();
            hashtable.Add(":username", username);
            hashtable.Add(":company", company);
            hashtable.Add(":applno", applNo);
            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }

        #endregion

        #region " | HeadHeld Application | "

        public DataTable getApplicationListWithHandHeld(string username, string company)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"
                SELECT a.applno,a.applname,a.description,a.url
                FROM user_profile up
                INNER JOIN user_profile_group upg ON up.empno=upg.empno  
                AND up.comp_code = upg.company_code
                INNER JOIN application a ON upg.applno=a.applno
                AND upg.company_code = a.company_code
                WHERE 1=1 
                AND up.empno =:username
                AND up.comp_code =:company
                AND upg.status='Y'
                AND up.status='A'
                AND a.status='Y'
                AND a.PDAFlag='Y'
                ORDER BY a.applname ASC
            ");

            hashtable = new Hashtable();
            hashtable.Add(":username", username);
            hashtable.Add(":company", company);
            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }

        public DataTable getApplicationListByApplNoWithHandHeld(string username, string company, string applNo)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"
                SELECT a.applno,a.applname,a.description,a.url
                FROM user_profile up
                INNER JOIN user_profile_group upg ON up.empno=upg.empno  
                AND up.comp_code = upg.company_code
                INNER JOIN application a ON upg.applno=a.applno
                AND upg.company_code = a.company_code
                WHERE 1=1 
                AND up.empno =:username
                AND up.comp_code =:company
                AND upg.applno =:applno
                AND upg.status='Y'
                AND up.status='A'
                AND a.status='Y'
                AND a.PDAFlag='Y'
                ORDER BY a.applname ASC
            ");

            hashtable = new Hashtable();
            hashtable.Add(":username", username);
            hashtable.Add(":company", company);
            hashtable.Add(":applno", applNo);
            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }

        public String insertTokenApp(string username, string password, string token
                                    , string applNo, string appName, string fcgroup
                                    , string division, string language, string company,string funcno = "")
        {
            try
            {
                dbEngine = new DBConnectionsEngine();
                Conn = new DatabaseProxy().getAUTHConnection();
                strSql = String.Format(@"
                    INSERT INTO APPTOKEN(APPTOKEN,TOKEN,APPLNO,APPLNAME,FUNCTION_GROUP,DIVISION,EMPNO,PASSWORD,CRDATE,LANG,COMPANY_CODE,FUNCNO)
                    VALUES((SELECT nvl(MAX(APPTOKEN),0)+1 FROM APPTOKEN),'{0}','{1}','{2}','{3}','{4}','{5}','{6}',sysdate,'{7}','{8}','{9}')
                ", token, applNo, appName, fcgroup, division, username, password, language, company, funcno);

                dbEngine.ExecuteNonQuery(strSql, Conn);

                return token;
            }
            catch (Exception)
            {
                return null;
            }

        }

        #endregion

        #region " | HeadHeld Function | "




        public DataTable getFunctionListWithHandHeld(string username, string company, string groupno, string applno)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"
                select fa.Funcno as function_no,
                fa.funcname as function_name,
                fa.mainscreen as function_url
                from user_profile up,
                user_profile_group upg,
                function_application fa,
                map_function map
                where up.Empno = upg.empno
                and up.comp_code = upg.company_code
                and map.funcno = fa.funcno
                and map.company_code = fa.company_code
                and map.groupno = upg.groupno
                and map.company_code = upg.company_code
                and map.applno = upg.applno
                and map.company_code = upg.company_code
                and fa.applno = upg.applno
                and fa.company_code = upg.company_code
                and upg.applno =:applno
                and upg.groupno =:groupno
                and up.empno =:username
                and up.pdaflag = 'Y'
                and fa.pdaflag='Y'
                and map.access_flag='Y'
                and fa.mainscreen IS NOT NULL
                and upg.company_code =:company
                and up.comp_code =:company
                and fa.company_code =:company
                and map.company_code =:company
                and up.status='A'
                and upg.status='Y'
                and fa.status='Y'
                order by fa.funcname asc
            ");

            hashtable = new Hashtable();
            hashtable.Add(":username", username);
            hashtable.Add(":company", company);
            hashtable.Add(":applno", applno);
            hashtable.Add(":groupno", groupno);
            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }
        
        public DataTable getFunctionListByApplNoWithHandHeld(string username, string company, string groupno, string applno, string funcNo)
        {
            dbEngine = new DBConnectionsEngine();
            Conn = new DatabaseProxy().getAUTHConnection();
            strSql = String.Format(@"
                select fa.Funcno as function_no,
                fa.funcname as function_name,
                fa.mainscreen as function_url
                from user_profile up,
                user_profile_group upg,
                function_application fa,
                map_function map
                where up.Empno = upg.empno
                and up.comp_code = upg.company_code
                and map.funcno = fa.funcno
                and map.company_code = fa.company_code
                and map.groupno = upg.groupno
                and map.company_code = upg.company_code
                and map.applno = upg.applno
                and map.company_code = upg.company_code
                and fa.applno = upg.applno
                and fa.company_code = upg.company_code
                and upg.applno =:applno
                and upg.groupno =:groupno
                and up.empno =:username
                and up.pdaflag = 'Y'
                and fa.pdaflag='Y'
                and map.access_flag='Y'
                and fa.mainscreen IS NOT NULL
                and upg.company_code =:company
                and up.comp_code =:company
                and fa.company_code =:company
                and map.company_code =:company
                and fa.funcno=:funcno
                and up.status='A'
                and upg.status='Y'
                and fa.status='Y'
            ");

            hashtable = new Hashtable();
            hashtable.Add(":username", username);
            hashtable.Add(":company", company);
            hashtable.Add(":groupno", groupno);
            hashtable.Add(":applno", applno);
            hashtable.Add(":funcno", funcNo);
            return dbEngine.GetDataTable(strSql, hashtable, Conn);
        }

        #endregion
    }
}
